﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace BanHangCshap.Class
{
    class PhieuChuyenKho : Database
    {
        #region ***** Fields & Properties *****
        private string _maphieu;
        public string maphieu
        {
            get
            {
                return _maphieu;
            }
            set
            {
                _maphieu = value;
            }
        }
        private string _ngaylap;
        public string ngaylap
        {
            get
            {
                return _ngaylap;
            }
            set
            {
                _ngaylap = value;
            }
        }
        private double _sotien;
        public double sotien
        {
            get
            {
                return _sotien;
            }
            set
            {
                _sotien = value;
            }
        }
        private string _sotienchu;
        public string sotienchu
        {
            get
            {
                return _sotienchu;
            }
            set
            {
                _sotienchu = value;
            }
        }
        private string _tendn;
        public string tendn
        {
            get
            {
                return _tendn;
            }
            set
            {
                _tendn = value;
            }
        }
        private string _tukho;
        public string tukho
        {
            get
            {
                return _tukho;
            }
            set
            {
                _tukho = value;
            }
        }
        private string _denkho;
        public string denkho
        {
            get
            {
                return _denkho;
            }
            set
            {
                _denkho = value;
            }
        }
        private string _ghichu;
        public string ghichu
        {
            get
            {
                return _ghichu;
            }
            set
            {
                _ghichu = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public PhieuChuyenKho()
        {
        }
        public PhieuChuyenKho(string maphieu)
        {
            this.maphieu = maphieu;
            //string sql = "select * from PhieuChuyenKho  ";
            //sql += "where maphieu = '" + maphieu + "'    ";
            //foreach (DataRow dr in getDataTable(sql).Rows)
            //{
            //    this.maphieu = maphieu;
            //    this.tukho = dr["tukho"].ToString().Trim();
            //    this.denkho = dr["denkho"].ToString().Trim();
            //    this.tendn = dr["tendn"].ToString().Trim();
            //    this.ghichu = dr["ghichu"].ToString().Trim();
            //    this.ngaylap = (DateTime)dr["ngaylap"];
            //    this.sotien = double.Parse(dr["sotien"].ToString());
            //    this.sotienchu = dr["sotienchu"].ToString().Trim();
            //}
        }
        //public PhieuChuyenKho(string maphieu, string ngaylap, int trigia, string tendn, string tukho, string denkho)
        //{
        //    this.maphieu = maphieu;
        //    this.ngaylap = ngaylap;
        //    this.trigia = trigia;
        //    this.tendn = tendn;
        //    this.tukho = tukho;
        //    this.denkho = denkho;
        //}
        #endregion
        public Boolean themPhieuChuyenKho()
        {
            string sql = "insert into PhieuChuyenKho(maphieu,ngaylap,sotien,sotienchu,tendn,tukho,denkho,ghichu) ";
            sql += "values ('" + maphieu + "','" + ngaylap + "'," + sotien + ",N'" + sotienchu + "','" + tendn + "','" + tukho + "','" + denkho + "',N'" + ghichu + "')";
            return executeQueryTrans(sql);
        }
        public Boolean xoaPhieuChuyenKho(string maphieu)
        {
            string sql = "delete PhieuChuyenKho where maphieu = '" + maphieu + "'";
            return executeQueryTrans(sql);
        }
        public DataTable layThongTinPhieuChuyenKho(string maphieu)
        {
            string sql = "select * from PhieuChuyenKho  ";
            sql += "where maphieu = '" + maphieu + "'    ";
            return getDataTable(sql);
        }
        public DataTable layDSPhieuChuyenKhoCT(string maphieu)
        {
            string sql = "select a.mahang,tenhang,a.dvt,slchuaquydoi,slquydoi, slnhapxuat,quycach,dgnhapxuat,thanhtien from PhieuChuyenKhoCT a, HangHoa b where maphieu = '" + maphieu + "' and a.mahang= b.mahang";
            return getDataTable(sql);
        }
        public double laySoPhieuChuyenKhoTrongNgay(string ngay)
        {
            string sql = "SELECT max(convert(int,substring(maphieu,12,len(maphieu)-10))) FROM PhieuChuyenKho WHERE convert(varchar(10),ngaylap,103)='" + ngay + "'";
            return getNumberValue(sql);
        }
        public DataTable timKiemPhieuChuyenKho(string tungay, string denngay)
        {
            string sql = "select distinct a.maphieu,  ngaylap,  kho1.tenkho tukho, kho2.tenkho as denkho,sotien,sotienchu   ";

            sql += "from PhieuChuyenKho a, Kho as kho1, Kho as kho2 ";
            sql += "where convert(datetime,ngaylap, 103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaylap, 103) <= convert(datetime,'" + denngay + "',103)   and a.tukho = kho1.makho and a.denkho = kho2.makho ";

            return getDataTable(sql);
        }
        public DataTable inPhieuChuyenKho(string tendoanhnghiep, string diachi, string dienthoai, string maphieu)
        {
            string sql = "select N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi, N'" + dienthoai + "' as dienthoai, a.maphieu, convert(varchar(10),ngaylap,103) as ngaylap, (N'Chuyển từ: ' + kho1.tenkho + N' đến : ' + kho2.tenkho) as thongtinchuyen,b.mahang, tenhang, ";
            sql += "b.dvt,slchuaquydoi,quycach,dgnhapxuat,thanhtien,sotien,sotienchu ";
            sql += "from PhieuChuyenKho a, PhieuChuyenKhoCT b,Kho as kho1, Kho as kho2, HangHoa c ";
            sql += "where A.maphieu = '" + maphieu + "' and a.maphieu = b.maphieu and a.tukho = kho1.makho and a.denkho = kho2.makho ";
            sql += "and b.mahang = c.mahang";
            return getDataTable(sql);
        }

    }
}
